﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.Odbc;
using System.IO;

namespace SQLMonitor
{
    public partial class ProcCacheDetails : Form
    {
        public OdbcConnection ProcCon;
        OdbcCommand MyCom;
        OdbcDataAdapter Ad;
        DataSet ds;
        public bool pre2005;
        string RowCount;
              
        public ProcCacheDetails(bool version)
        {
            InitializeComponent();
            pre2005 = version;
            cmbRows.Text = "100";
        }

        /// <summary>
        /// Called when the form loads
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ProcCacheDetails_Load(object sender, EventArgs e)
        {
            RenderInfo();           
        }

        private void RenderInfo()
        {
            // How many rows to display
            if (cmbRows.Text == "All")
            {
                RowCount = "";
            }
            else
            {
                RowCount = "top " + cmbRows.Text;
            }

            try
            {
                if (pre2005)
                {
                    MyCom = new OdbcCommand(@"SELECT " + RowCount + @" UseCounts, RefCounts,CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL FROM syscacheobjects
                                                ORDER BY dbid,usecounts DESC,objtype");
                }
                else
                {

                    MyCom = new OdbcCommand(@"select  " + RowCount + @" cp.bucketid, cp.refcounts, qs.plan_generation_num, cp.usecounts, (cp.size_in_bytes / 1024) As size_in_kb, cp.cacheobjtype, cp.objtype, mce.in_use_count, qs.creation_time, qs.last_execution_time, qs.execution_count, qs.total_worker_time, qs.total_physical_reads, qs.total_logical_reads, qs.total_logical_writes, qs.total_elapsed_time, mce.is_dirty, mce.disk_ios_count, mce.original_cost, mce.current_cost, qp.query_plan
                                            from sys.dm_exec_cached_plans cp
                                            left outer join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle
                                            left outer join sys.dm_os_memory_cache_entries mce on mce.memory_object_address = cp.memory_object_address
                                            left outer join sys.dm_exec_requests req on req.plan_handle = cp.plan_handle
                                            OUTER APPLY sys.dm_exec_query_plan(cp.[plan_handle]) AS qp
                                            order by usecounts desc");
                }

                
                MyCom.Connection = ProcCon;

                ds = new DataSet("ProcCacheData");
                Ad = new OdbcDataAdapter(MyCom);

                Ad.Fill(ds);

                dgProcCache.AutoGenerateColumns = true;
                dgProcCache.DataSource = ds;
                dgProcCache.DataMember = "Table";
            }

            catch (Exception MyE)
            {
                MessageBox.Show(MyE.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Open a file to write to
            string Path = Directory.GetCurrentDirectory();
            FileStream FS = new FileStream(Path + "\\ProcedureCacheDetails.csv", FileMode.Append, FileAccess.Write, FileShare.None);
            StreamWriter sw = new StreamWriter(FS, Encoding.ASCII);

            // Write the data
            sw.Write("Date Time,");

            // Write out the column headings
            foreach (DataColumn col in ds.Tables["Table"].Columns)
            {
                sw.Write(col.ColumnName + ",");
            }

            sw.Write("\n");

            // now write out each row and each column
            foreach (DataRow row in ds.Tables["Table"].Rows)
            {
                sw.Write(DateTime.Now.ToString() + ",");

                for (int index = 0; index < ds.Tables["Table"].Columns.Count; index++)
                {
                    sw.Write(row[index].ToString().Replace("\r\n", " ") + ",");
                }
                sw.Write("\n");
            }

            sw.Write("\n\n");

            // Close The File
            sw.Flush();
            FS.Flush();
            sw.Close();
            FS.Close();
            sw.Dispose();
            FS.Dispose();

            MessageBox.Show("Data Saved");
        }

        /// <summary>
        /// Called when the user presses the refresh button
        /// Should just re-call the RenderInfo function to update the form
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnRefresh_Click(object sender, EventArgs e)
        {
            RenderInfo();
        }
        
    }
}
